Create New Measures for KPIs 12

- (1)Click to New Measure ---> (2) Rename the Measure by whatever you need by deleting “Measure” word then rename it ---> (3) Write a DAX after “=” depend on what you would like to create.


1) Create OpenEmail Measure to calculate the Emails that are opened

- (1) Click to New Measure

- (2) Rename the Measure to OpenEmail

- (3) Write DAX  OpenEmail = COUNTROWS(FILTER('MailChimp','MailChimp'[EmailOpenCount]<>"0"))

- (4) Click to 


2) Create TotalEmail Measure to calculate the Total Emails that we sent

     DAX TotalEmail = COUNTROWS(FILTER('MailChimp','MailChimp'[Status]="sent"))

3) Create OpenRate Measure to calculate the Percentage of the emails which are opened based on the Total Emails that we have sent.

     DAX OpenRate  = DIVIDE([OpenEmail], [Total Email])

4) Create BounceEmails Measure to calculate the Bounces Email

     DAX  Bounceemail = COUNTROWS(FILTER('MailChimp','MailChimp'[Status]<>"sent"))

5) Create BounceRate Measure to calculate the Percentage of the Bounce Emails based on the Total Emails that we have sent.

      DAX BounceRate = DIVIDE([BounceEmail], [Total Email])

6) Create Recipients Measure to calculate the Recipients

      DAX Recipients = DISTINCTCOUNT(MailChimp[Email])

7) Create SortDate Column to sort the data from the previous to current month

     DAX Sortdate =  FORMAT('MailChimp'[SentDate],"MMMM""-""YYYY")

8) Create PreMOpenRate. This is going to be used as a Target in KPI, so that we can compare the OpenRate from the Current Month to Previous Month.

     DAX PreMOpenRate =  CALCULATE([OpenRate], PREVIOUSMONTH(MailChimp[SortDate]))

9) Create PreMBounceRate for BounceRate KPI

      DAX  PreMBounceRate = CALCULATE( [BounceRate],PREVIOUSMONTH(MailChimp[SortDate]))

10) Create PreMRecipients for Recipients KPI

     DAX PreMRecipients = CALCULATE( [Recipients], PREVIOUSMONTH(MailChimp[SortDate]))

11) Create PreMTotalEmail for Total Email KPI

 DAX PreMTotalEmail = CALCULATE([Total Email], PREVIOUSMONTH(MailChimp[SortDate]))

Format the OpenRate/BunceRate to Percentage

- (1) Double Click to OpenRate Measure ---> (2) Click to Modeling ---> (3) Click to % icon.